package backend;

import java.sql.*;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;

public class Warehouse
{
	
	private Connection sqlCon;
	private boolean isOpen = false;
	
	public void open() throws SQLException
	{
		try
		{
			DriverManager.registerDriver(new com.mysql.jdbc.Driver());
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		
		sqlCon = DriverManager.getConnection("jdbc:mysql://127.0.0.1/warehouse_bagtracking", "root", "derpderpderp");
		isOpen = true;
	}
	
	public void close() throws SQLException
	{
		isOpen = false;
		sqlCon.close();
	}
	
	public Fact getFact(int id)
	{
		Fact f = null;
		
		try
		{
			Statement s = sqlCon.createStatement();
			
			ResultSet res = s.executeQuery(
					"SELECT * " +
					"FROM fact_stay " +
					"WHERE f_ID = "+id);
			
			if (res.next())
			{
				f = new Fact(
						this,
						id,
						res.getInt("time_ID"),
						res.getInt("date_ID"),
						res.getInt("bag_ID"),
						res.getInt("location_ID"),
						res.getInt("duration"),
						(res.getString("status").equals("ok") ? false : true) );
			}
			
			res.close();
			s.close();
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			System.exit(1);
		}
		
		return f;
	}
	
	public Fact getLatestFactByBag(int bagId)
	{
		try
		{
			Statement s = sqlCon.createStatement();
			
			ResultSet res = s.executeQuery(
					"SELECT f_ID " +
					"FROM fact_stay " +
					"WHERE bag_ID = " + bagId + " " +
					"ORDER BY f_ID DESC " +
					"LIMIT 1");
			
			if (res.next())
			{
				return getFact(res.getInt("f_ID"));
			}
			
			res.close();
			s.close();
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			System.exit(1);
		}
		
		return null;
	}
	
	public void updateFactDuration(Fact f)
	{
		try
		{
			Statement s = sqlCon.createStatement();
			
			s.executeUpdate(
				"UPDATE fact_stay " +
				"SET `duration` = " + f.getDuration() + " " +
				"WHERE f_ID = " + f.getFactId());
			
			s.close();
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			System.exit(1);
		}
	}
	
	public Location getLocation(int id)
	{
		Location l = null;
		
		try
		{
			Statement s = sqlCon.createStatement();
			
			ResultSet res = s.executeQuery(
					"SELECT l_country, l_airport, l_tag_reader_location, l_location_type " +
					"FROM dimension_location " +
					"WHERE l_ID = " + id);
			if (res.next())
			{
				l = new Location(
						id,
						res.getString("l_country"),
						res.getString("l_airport"),
						res.getString("l_tag_reader_location"),
						Location.getType(res.getString("l_location_type")) );
			}
			
			res.close();
			s.close();
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			System.exit(1);
		}
		
		return l;
	}
	
	public Bag getBag(int id)
	{
		Bag b = null;
		
		try
		{
			Statement s = sqlCon.createStatement();
			
			ResultSet res = s.executeQuery(
					"SELECT b_license_plate, b_originating_airport, b_route_id " +
					"FROM dimension_bag " +
					"WHERE b_ID = " + id);
			if (res.next())
			{
				long license = res.getLong("b_license_plate");
				b = new Bag(id, license, res.getString("b_originating_airport"), res.getInt("b_route_ID"), getRouteLegsByBagId(id));
			}
			
			res.close();
			s.close();
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			System.exit(1);
		}
		
		return b;
	}
	
	public Bag getBagByLicensePlate(long license)
	{
		Bag b = null;
		
		try
		{
			Statement s = sqlCon.createStatement();
			
			ResultSet res = s.executeQuery(
					"SELECT b_ID, b_originating_airport, b_route_id " +
					"FROM dimension_bag " +
					"WHERE b_license_plate = " + license);
			if (res.next())
			{
				int id = res.getInt("b_ID");
				b = new Bag(id, license, res.getString("b_originating_airport"), res.getInt("b_route_ID"), getRouteLegsByBagId(id));
			}
			
			res.close();
			s.close();
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			System.exit(1);
		}
		
		return b;
	}
	
	public RouteLeg[] getRouteLegsByBagId(int id)
	{
		RouteLeg[] rls = null;
		
		try
		{
			Statement s = sqlCon.createStatement();
			
			ResultSet res = s.executeQuery(
					"SELECT leg.rl_ID, leg.rl_destination, leg.rl_airline_name, rb.rb_index " +
					"FROM dimension_bag bag " +
					"JOIN dimension_route route " +
						"ON bag.b_ID = " + id + " " +
						"AND route.r_ID = bag.b_route_ID " +
					"JOIN dimension_route_bridge rb " +
						"ON rb.rb_route_ID = route.r_ID " +
					"JOIN dimension_route_leg leg " +
						"ON leg.rl_ID = rb.rb_route_leg_ID");
			res.last();
			int rows = res.getRow();
			res.beforeFirst();
			rls = new RouteLeg[rows];
			
			int i = 0;
			while (res.next())
			{
				rls[i++] = new RouteLeg(
						res.getInt("leg.rl_ID"),
						res.getString("leg.rl_destination"),
						res.getString("leg.rl_airline_name"),
						res.getInt("rb.rb_index"));
			}
			
			res.close();
			s.close();
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			System.exit(1);
		}
		
		return rls;
	}
	
	public int getRouteLegId(String dest, String airline) {
		
		try
		{
			Statement s = sqlCon.createStatement();
			
			ResultSet res = s.executeQuery(
					"SELECT rl_ID " +
					"FROM dimension_route_leg " +
					"WHERE rl_destination = '" + dest + "' " +
					"AND rl_airline_name = '" + airline + "'");
			if (res.next())
			{
				return res.getInt("rl_ID");
			}
			
			res.close();
			s.close();
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			System.exit(1);
		}
		
		return -1;
	}
	
	public DateTime getDateTimeFromDate(Date in)
	{
		Calendar cal = Calendar.getInstance();
		cal.setTime(in);
		
		try
		{
			Statement s = sqlCon.createStatement();
			
			ResultSet res = s.executeQuery(
					"SELECT d_ID, d_day_type " +
					"FROM dimension_date " +
					"WHERE d_day = " + cal.get(Calendar.DAY_OF_MONTH) + " " +
					"AND d_month = " + cal.get(Calendar.MONTH) + " " +
					"AND d_year = " + cal.get(Calendar.YEAR));
			res.next();
			
			int dId = res.getInt("d_ID");
			String dType = res.getString("d_day_type");
			
			res = s.executeQuery(
					"SELECT t_ID, t_time_type " +
					"FROM dimension_time " +
					"WHERE t_second = " + cal.get(Calendar.SECOND) + " " +
					"AND t_minute = " + cal.get(Calendar.MINUTE) + " " +
					"AND t_hour = " + cal.get(Calendar.HOUR_OF_DAY));
			res.next();
			
			int tId = res.getInt("t_ID");
			String tType = res.getString("t_time_type");

			res.close();
			s.close();
			
			return new DateTime(in.getTime()/1000, dId, tId, dType, tType);
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			System.exit(1);
		}
		
		// This will never be reached, due to the System.exit(1) call above...
		return null;
	}

	public DateTime getDateTime(int dateId, int timeId)
	{
		
		try
		{
			Statement s = sqlCon.createStatement();
			
			ResultSet res = s.executeQuery(
					"SELECT * " +
					"FROM dimension_date " +
					"WHERE d_ID = " + dateId);
			res.next();
			
			String dType = res.getString("d_day_type");
			int dYear = res.getInt("d_year");
			int dMonth = res.getInt("d_month");
			int dDay = res.getInt("d_day");
			
			res = s.executeQuery(
					"SELECT * " +
					"FROM dimension_time " +
					"WHERE t_ID = " + timeId);
			res.next();
			
			String tType = res.getString("t_time_type");
			int tHour = res.getInt("t_hour");
			int tMinute = res.getInt("t_minute");
			int tSecond = res.getInt("t_second");
			
			Calendar tmpCal = Calendar.getInstance();
			tmpCal.set(dYear, dMonth, dDay, tHour, tMinute, tSecond);

			res.close();
			s.close();
			
			return new DateTime(tmpCal.getTimeInMillis()/1000, dateId, timeId, dType, tType);
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			System.exit(1);
		}
		
		return null;
	}

	public void insertFact(Fact f)
	{
		try
		{
			Statement s = sqlCon.createStatement();
			
			s.executeUpdate(
					"INSERT INTO fact_stay " +
					"(`date_ID`, `time_ID`, `location_ID`, `bag_ID`, `duration`, `status`) " +
					"VALUES (" +
						f.getDateId() + ", " +
						f.getTimeId() + ", " +
						f.getLocationId() + ", " +
						f.getBagId() + ", " +
						f.getDuration() + ", " +
						"'" + (f.getStatus() ? "lost" : "ok") + "')",
					Statement.RETURN_GENERATED_KEYS);
			
			ResultSet rs = s.getGeneratedKeys();
			rs.next();
			f.setFactId(rs.getInt(1));
			
			rs.close();
			s.close();
			
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			System.exit(1);
		}
	}
	
	public void insertLocation(Location l)
	{
		try
		{
			Statement s = sqlCon.createStatement();
			
			s.executeUpdate(
					"INSERT INTO dimension_location " +
					"(`l_ID`, `l_country`, `l_airport`, `l_tag_reader_location`, `l_location_type`) " +
					"VALUES (" +
						l.getId() + ", " +
						"'" + l.getCountry() + "', " +
						"'" + l.getAirport() + "', " +
						"'" + l.getReaderLocation() + "', " +
						"'" + l.getReaderType() + "')");
			
			s.close();
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			System.exit(1);
		}
	}
	
	public void insertBag(Bag b)
	{
		try
		{
			Statement s = sqlCon.createStatement();
			
			// Insert routelegs if necessary
			for (RouteLeg rl : b.getBagRoute())
			{
				if (rl.getId() == -1)
				{
					insertRouteLeg(rl);
				}
			}
			
			// Insert route if necessary
			if (b.getRouteId() == -1 && !b.getOriginatingAirport().equals("nul"))
			{
				
				int matchingRoute = findRouteByLegs(b.getBagRoute());
				
				// Was there a matching route?
				if (matchingRoute != -1)
				{
					b.setRouteId(matchingRoute);
				}
				// Was there no matching route?
				else
				{
					int newRId = insertRoute(b.getBagRoute().length);
					b.setRouteId(newRId);
					
					updateRoute(newRId, b.getBagRoute());
				}
				
			}
			
			// Insert bag
			s.executeUpdate(
					"INSERT INTO dimension_bag " +
					"(`b_license_plate`, `b_originating_airport`, `b_route_ID`) " +
					"VALUES (" +
						b.getLicensePlate() + ", " +
						"'" + b.getOriginatingAirport() + "', " +
						(b.getRouteId() == -1 ? "NULL" : b.getRouteId()) + ")",
					Statement.RETURN_GENERATED_KEYS);
			
			ResultSet rs = s.getGeneratedKeys();
			rs.next();
			b.setId(rs.getInt(1));
			
			rs.close();
			s.close();
			
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			System.exit(1);
		}
	}
	
	public int insertRoute(int length)
	{
		try
		{
			Statement s = sqlCon.createStatement();
			
			// Insert a new route and add the route bridges
			s.executeUpdate(
					"INSERT INTO dimension_route " +
					"(`r_length`) " +
					"VALUES (" + length + ")",
					Statement.RETURN_GENERATED_KEYS);
			
			ResultSet rs = s.getGeneratedKeys();
			rs.next();
			int newKey = rs.getInt(1);
			
			rs.close();
			s.close();
			
			return newKey;
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			System.exit(1);
		}
		return -1;
	}
	
	public void insertRouteLeg(RouteLeg routeLeg)
	{
		try
		{
			Statement s = sqlCon.createStatement();
			
			s.executeUpdate(
					"INSERT INTO dimension_route_leg " +
					"(`rl_destination`, `rl_airline_name`) " +
					"VALUES (" +
						"'" + routeLeg.getDestination() + "', " +
						"'" + routeLeg.getAirline() + "')",
					Statement.RETURN_GENERATED_KEYS);
			
			ResultSet rs = s.getGeneratedKeys();
			rs.next();
			routeLeg.setId(rs.getInt(1));
			
			rs.close();
			s.close();
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			System.exit(1);
		}
	}
	
	public int findRouteByLegs(RouteLeg[] legs)
	{
		
		ArrayList<Integer> routeCandidates = new ArrayList<Integer>();
		
		try
		{
			Statement s = sqlCon.createStatement();
			ResultSet res = null;
			
			ArrayList<Integer> routesToKeep = new ArrayList<Integer>();
			boolean first = true;
			for (RouteLeg rl : legs)
			{
				routesToKeep.clear();
				
				res = s.executeQuery(
						"SELECT r.r_ID " +
						"FROM dimension_route r " +
						"JOIN dimension_route_bridge rb " +
							"ON rb.rb_route_ID = r.r_ID " +
						"WHERE r.r_length = " + legs.length + " " +
							"AND rb.rb_route_leg_ID = " + rl.getId() + " " +
							"AND rb.rb_index = " + rl.getIndex());
				while (res.next())
				{
					routesToKeep.add(res.getInt("r.r_ID"));
				}
				
				if (first)
				{
					routeCandidates.addAll(routesToKeep);
					first = false;
				}
				else
				{
					ArrayList<Integer> newCandidates = new ArrayList<Integer>();
					for (int cand : routeCandidates)
					{
						if (routesToKeep.contains(cand))
						{
							newCandidates.add(cand);
						}
					}
					routeCandidates = newCandidates;
				}
			}
			
			if (res != null)
				res.close();
			s.close();
			
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			System.exit(1);
		}
		
		if (routeCandidates.size() == 1)
		{
			return routeCandidates.get(0);
		}
		else
		{
			return -1;
		}
	}

	public void updateRoute(int routeId, RouteLeg[] legs)
	{
		try
		{
			Statement s = sqlCon.createStatement();
			
			// Clear the previous route leg bridges for this route
			s.executeUpdate(
					"DELETE FROM dimension_route_bridge " +
					"WHERE rb_route_ID = " + routeId);
			
			// Insert new route leg bridges
			for (RouteLeg rl : legs)
			{
				s.executeUpdate(
						"INSERT INTO dimension_route_bridge " +
						"(`rb_route_ID`, `rb_route_leg_ID`, `rb_index`) " +
						"VALUES (" + routeId + ", " +
							rl.getId() + ", " +
							rl.getIndex() + ")" );
			}
			
			// Update route with new length
			s.executeUpdate(
					"UPDATE dimension_route " +
					"SET `r_length` = " + legs.length + " " +
					"WHERE r_ID = " + routeId);
			
			s.close();
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			System.exit(1);
		}
	}

	public void updateBagRoute(int id, int routeId)
	{
		try
		{
			Statement s = sqlCon.createStatement();
			
			s.executeUpdate(
				"UPDATE dimension_bag " +
				"SET `b_route_ID` = " + routeId + " " +
				"WHERE b_ID = " + id);
			
			s.close();
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			System.exit(1);
		}
	}
	
	/// ANALYTICAL QUERYING \\\
	public Connection getSqlCon()
	{
		return sqlCon;
	}
	
}
